from flask import Blueprint, jsonify, request, current_app
import os
import sqlite3
import pandas as pd
import json
from config import Config
from database import get_db, allowed_file, read_file_to_dataframe

scenario_bp = Blueprint('scenario', __name__)

@scenario_bp.route('/', methods=['GET'])
def get_scenarios():
    """获取所有场景"""
    db = get_db()
    scenarios = db.execute('SELECT * FROM scenarios ORDER BY created_at DESC').fetchall()
    
    result = []
    for scenario in scenarios:
        result.append({
            'id': scenario['id'],
            'name': scenario['name'],
            'description': scenario['description'],
            'scenario_type': scenario['scenario_type'],
            'db_connection_id': scenario['db_connection_id'],
            'created_at': scenario['created_at'],
            'updated_at': scenario['updated_at']
        })
    
    return jsonify(result)

@scenario_bp.route('/', methods=['POST'])
def create_scenario():
    """创建新场景"""
    required_fields = ['name', 'scenario_type']
    
    for field in required_fields:
        if field not in request.json:
            return jsonify({'error': f'缺少必填字段: {field}'}), 400
    
    db = get_db()
    try:
        cursor = db.execute(
            '''INSERT INTO scenarios 
               (name, description, scenario_type, db_connection_id) 
               VALUES (?, ?, ?, ?)''',
            (request.json['name'], request.json.get('description', ''), 
             request.json['scenario_type'], request.json.get('db_connection_id'))
        )
        db.commit()
        
        scenario_id = cursor.lastrowid
        return jsonify({
            'id': scenario_id,
            'name': request.json['name'],
            'message': '场景创建成功'
        }), 201
    except sqlite3.Error as e:
        db.rollback()
        return jsonify({'error': f'创建场景失败: {str(e)}'}), 500

@scenario_bp.route('/<int:scenario_id>', methods=['GET'])
def get_scenario(scenario_id):
    """获取单个场景详情"""
    db = get_db()
    scenario = db.execute('SELECT * FROM scenarios WHERE id = ?', (scenario_id,)).fetchone()
    
    if scenario is None:
        return jsonify({'error': '场景不存在'}), 404
    
    # 获取场景的数据导入记录
    imports = db.execute(
        'SELECT * FROM data_imports WHERE scenario_id = ?',
        (scenario_id,)
    ).fetchall()
    
    import_records = []
    for import_record in imports:
        import_records.append({
            'id': import_record['id'],
            'file_name': import_record['file_name'],
            'file_type': import_record['file_type'],
            'record_count': import_record['record_count'],
            'imported_at': import_record['imported_at']
        })
    
    # 获取场景的参数
    params = db.execute(
        'SELECT * FROM scenario_params WHERE scenario_id = ?',
        (scenario_id,)
    ).fetchall()
    
    param_records = []
    for param in params:
        param_records.append({
            'id': param['id'],
            'name': param['param_name'],
            'value': param['param_value'],
            'type': param['param_type']
        })
    
    return jsonify({
        'id': scenario['id'],
        'name': scenario['name'],
        'description': scenario['description'],
        'scenario_type': scenario['scenario_type'],
        'db_connection_id': scenario['db_connection_id'],
        'created_at': scenario['created_at'],
        'updated_at': scenario['updated_at'],
        'imports': import_records,
        'params': param_records
    })

@scenario_bp.route('/<int:scenario_id>', methods=['PUT'])
def update_scenario(scenario_id):
    """更新场景信息"""
    db = get_db()
    scenario = db.execute('SELECT * FROM scenarios WHERE id = ?', (scenario_id,)).fetchone()
    
    if scenario is None:
        return jsonify({'error': '场景不存在'}), 404
    
    data = request.json
    
    try:
        db.execute(
            '''UPDATE scenarios 
               SET name = ?, description = ?, scenario_type = ?, db_connection_id = ?,
               updated_at = CURRENT_TIMESTAMP
               WHERE id = ?''',
            (data.get('name', scenario['name']),
             data.get('description', scenario['description']),
             data.get('scenario_type', scenario['scenario_type']),
             data.get('db_connection_id', scenario['db_connection_id']),
             scenario_id)
        )
        db.commit()
        
        return jsonify({
            'id': scenario_id,
            'message': '场景更新成功'
        })
    except sqlite3.Error as e:
        db.rollback()
        return jsonify({'error': f'更新场景失败: {str(e)}'}), 500

@scenario_bp.route('/<int:scenario_id>', methods=['DELETE'])
def delete_scenario(scenario_id):
    """删除场景"""
    db = get_db()
    scenario = db.execute('SELECT * FROM scenarios WHERE id = ?', (scenario_id,)).fetchone()
    
    if scenario is None:
        return jsonify({'error': '场景不存在'}), 404
    
    try:
        # 删除关联的数据导入记录和文件
        imports = db.execute('SELECT file_path FROM data_imports WHERE scenario_id = ?', (scenario_id,)).fetchall()
        
        db.execute('DELETE FROM data_imports WHERE scenario_id = ?', (scenario_id,))
        db.execute('DELETE FROM scenario_params WHERE scenario_id = ?', (scenario_id,))
        db.execute('DELETE FROM scenarios WHERE id = ?', (scenario_id,))
        db.commit()
        
        # 删除关联的文件
        for import_record in imports:
            file_path = import_record['file_path']
            if os.path.exists(file_path):
                os.remove(file_path)
        
        return jsonify({'message': '场景删除成功'})
    except sqlite3.Error as e:
        db.rollback()
        return jsonify({'error': f'删除场景失败: {str(e)}'}), 500

@scenario_bp.route('/<int:scenario_id>/params', methods=['POST'])
def add_scenario_param(scenario_id):
    """添加场景参数"""
    required_fields = ['param_name', 'param_value', 'param_type']
    
    for field in required_fields:
        if field not in request.json:
            return jsonify({'error': f'缺少必填字段: {field}'}), 400
    
    db = get_db()
    try:
        # 检查参数是否已存在
        existing_param = db.execute(
            'SELECT * FROM scenario_params WHERE scenario_id = ? AND param_name = ?',
            (scenario_id, request.json['param_name'])
        ).fetchone()
        
        if existing_param:
            # 更新现有参数
            db.execute(
                'UPDATE scenario_params SET param_value = ?, param_type = ? WHERE id = ?',
                (request.json['param_value'], request.json['param_type'], existing_param['id'])
            )
        else:
            # 添加新参数
            db.execute(
                'INSERT INTO scenario_params (scenario_id, param_name, param_value, param_type) VALUES (?, ?, ?, ?)',
                (scenario_id, request.json['param_name'], request.json['param_value'], request.json['param_type'])
            )
        
        db.commit()
        return jsonify({'message': '参数保存成功'})
    except sqlite3.Error as e:
        db.rollback()
        return jsonify({'error': f'保存参数失败: {str(e)}'}), 500

@scenario_bp.route('/<int:scenario_id>/imports', methods=['POST'])
def import_data(scenario_id):
    """导入数据文件"""
    if 'file' not in request.files:
        return jsonify({'error': '未上传文件'}), 400
    
    file = request.files['file']
    
    if file.filename == '':
        return jsonify({'error': '未选择文件'}), 400
    
    if file and allowed_file(file.filename):
        # 保存文件
        filename = f"scenario_{scenario_id}_{file.filename}"
        file_path = os.path.join(Config.UPLOAD_FOLDER, filename)
        file.save(file_path)
        
        # 读取文件并获取记录数
        df = read_file_to_dataframe(file_path)
        record_count = len(df) if df is not None else 0
        
        # 记录导入信息
        db = get_db()
        try:
            cursor = db.execute(
                '''INSERT INTO data_imports 
                   (scenario_id, file_name, file_path, file_type, record_count) 
                   VALUES (?, ?, ?, ?, ?)''',
                (scenario_id, file.filename, file_path, 
                 file.filename.rsplit('.', 1)[1].lower(), record_count)
            )
            db.commit()
            
            import_id = cursor.lastrowid
            return jsonify({
                'id': import_id,
                'file_name': file.filename,
                'record_count': record_count,
                'message': '文件导入成功'
            }), 201
        except sqlite3.Error as e:
            db.rollback()
            # 删除已保存的文件
            if os.path.exists(file_path):
                os.remove(file_path)
            return jsonify({'error': f'记录导入信息失败: {str(e)}'}), 500
    else:
        return jsonify({'error': '不支持的文件类型'}), 400

@scenario_bp.route('/<int:scenario_id>/imports/<int:import_id>/data', methods=['GET'])
def get_imported_data(scenario_id, import_id):
    """获取导入的数据"""
    db = get_db()
    import_record = db.execute(
        'SELECT * FROM data_imports WHERE id = ? AND scenario_id = ?',
        (import_id, scenario_id)
    ).fetchone()
    
    if import_record is None:
        return jsonify({'error': '导入记录不存在'}), 404
    
    # 读取文件内容
    df = read_file_to_dataframe(import_record['file_path'])
    if df is None:
        return jsonify({'error': '无法读取文件内容'}), 500
    
    # 获取分页参数
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)
    
    # 分页处理
    start = (page - 1) * per_page
    end = start + per_page
    paginated_data = df.iloc[start:end].to_dict('records')
    
    return jsonify({
        'data': paginated_data,
        'page': page,
        'per_page': per_page,
        'total': len(df),
        'total_pages': (len(df) + per_page - 1) // per_page
    })

@scenario_bp.route('/<int:scenario_id>/manual-input', methods=['POST'])
def manual_input_data(scenario_id):
    """手动输入数据"""
    if not request.json or not isinstance(request.json, dict) or 'data' not in request.json:
        return jsonify({'error': '无效的数据格式'}), 400
    
    data = request.json['data']
    if not isinstance(data, list) or len(data) == 0:
        return jsonify({'error': '数据必须是非空数组'}), 400
    
    # 将数据转换为DataFrame
    df = pd.DataFrame(data)
    
    # 保存为CSV文件
    filename = f"scenario_{scenario_id}_manual_input_{pd.Timestamp.now().strftime('%Y%m%d%H%M%S')}.csv"
    file_path = os.path.join(Config.UPLOAD_FOLDER, filename)
    
    # 确保目录存在
    os.makedirs(os.path.dirname(file_path), exist_ok=True)
    
    # 保存数据
    df.to_csv(file_path, index=False)
    
    # 记录导入信息
    db = get_db()
    try:
        cursor = db.execute(
            '''INSERT INTO data_imports 
               (scenario_id, file_name, file_path, file_type, record_count) 
               VALUES (?, ?, ?, ?, ?)''',
            (scenario_id, filename, file_path, 'csv', len(df))
        )
        db.commit()
        
        import_id = cursor.lastrowid
        return jsonify({
            'id': import_id,
            'file_name': filename,
            'record_count': len(df),
            'message': '手动输入数据保存成功'
        }), 201
    except sqlite3.Error as e:
        db.rollback()
        # 删除已保存的文件
        if os.path.exists(file_path):
            os.remove(file_path)
        return jsonify({'error': f'保存手动输入数据失败: {str(e)}'}), 500